package com.grep.extra.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.grep.base.db.DBUtils;
import com.grep.extra.dao.BaseDao;
import com.grep.extra.dao.WebSiteDao;
import com.grep.extra.factory.data.impl.SiteUrl;

public class WebSiteDAOSQL implements WebSiteDao {

	private static Logger logger = LoggerFactory.getLogger(WebSiteDAOSQL.class);

	// 不使用继承，使用组合
	BaseDao baseDao = new BaseDaoImpl();

	public boolean insertWebSite(String insertSQL) {
		return baseDao.insert(insertSQL);
	}

	public SiteUrl getWebSite(String siteid) {

		SiteUrl siteurl = new SiteUrl();

		String sql = " select * from web_site where id='" + siteid + "'";
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = DBUtils.getConnection();
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery(sql);
			while (rs.next()) {
				siteurl.setId(rs.getString("id"));
				siteurl.setSiteName(rs.getString("site_name"));
				siteurl.setSiteUrl(rs.getString("site_url"));
				siteurl.setSiteLogo(rs.getString("site_logo"));
				siteurl.setSiteDesc(rs.getString("site_desc"));
				siteurl.setSiteEnname(rs.getString("site_enname"));

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtils.close(rs);
			DBUtils.close(pstmt);
			DBUtils.close(conn);
		}

		return siteurl;
	}
	
	//分页形式得到website
	public List<SiteUrl> getWebSites(int start,int pagesize) {
		
		
		
		String sql = " select * from web_site limit "+start+","+pagesize;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
	    List<SiteUrl> list = new ArrayList<SiteUrl>();
		try {
			conn = DBUtils.getConnection();
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery(sql);
			while (rs.next()) {
				SiteUrl url = new SiteUrl();
				url.setId(rs.getString("id"));
				url.setSiteName(rs.getString("site_name"));
				url.setSiteUrl(rs.getString("site_url"));
				url.setSiteLogo(rs.getString("site_logo"));
				url.setSiteDesc(rs.getString("site_desc"));
				url.setSiteEnname(rs.getString("site_enname"));

				list.add(url);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtils.close(rs);
			DBUtils.close(pstmt);
			DBUtils.close(conn);
		}
		return list;
	}
	
	public List<SiteUrl> getSiteUrls() {
		String sql = " select * from web_site";
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<SiteUrl> list = new ArrayList<SiteUrl>();
		try {
			conn = DBUtils.getConnection();
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery(sql);
			while (rs.next()) {
				SiteUrl url = new SiteUrl();
				url.setId(rs.getString("id"));
				url.setSiteName(rs.getString("site_name"));
				url.setSiteUrl(rs.getString("site_url"));
				url.setSiteLogo(rs.getString("site_logo"));
				url.setSiteDesc(rs.getString("site_desc"));
				url.setSiteEnname(rs.getString("site_enname"));

				list.add(url);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtils.close(rs);
			DBUtils.close(pstmt);
			DBUtils.close(conn);
		}
		return list;
	}
	//得到website的总数量
	public int getWebSiteCount() {

		int count = 0;

		Connection conn = null;
		Statement stat = null;
		ResultSet result = null;
		String countsql = "select count(*) from web_site ";
		try {
			conn = DBUtils.getConnection();
			stat = conn.createStatement();
			result = stat.executeQuery(countsql);
			if (result.next()) {
				count = result.getInt(1);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtils.close(result);
			DBUtils.close(stat);
			DBUtils.close(conn);
		}

		return count;
	}
	
	public String getSiteIdBySiteName(String name) {

		String id = null;

		Connection conn = null;
		Statement stat = null;
		ResultSet result = null;

		String selectSiteNameSql = "select id from web_site where site_name='"
				+ name + "'";
		// ResultSet result = baseDao.select(selectSiteNameSql);
		try {
			conn = DBUtils.getConnection();
			stat = conn.createStatement();
			result = stat.executeQuery(selectSiteNameSql);
			if (result.next()) {
				id = result.getString(1);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtils.close(result);
			DBUtils.close(stat);
			DBUtils.close(conn);
		}
		return id;
	}
	public boolean DeleteWebSiteBySitename(String name) {

		String deletesql = "delete from web_site where site_name='" + name
				+ "'";

		return baseDao.insert(deletesql);
	}

}
